package ca.llsutherland.nfl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import ca.llsutherland.nfl.domain.Conference;
import ca.llsutherland.nfl.domain.Division;
import ca.llsutherland.nfl.exception.ValidationException;
import ca.llsutherland.nfl.helper.DivisionHelper;
import ca.llsutherland.nfl.util.StringUtil;

public class DivisionDao extends BaseDao {

private static DivisionDao instance = new DivisionDao();
	
	@Override
	public String getUnderlyingTableName() {
		return "DIVISION";
	}

	public static DivisionDao getInstance(){
		return instance;
	}
	
	private Division buildDivision(String sql) {
		Division division = null;
        ResultSet rs = super.findByQuery(sql);
        
        try {
            if(rs.next()){
            	Conference conference = ConferenceDao.getInstance().findById(rs.getLong("CONFERENCE_ID"));
				division = DivisionHelper.createNewDivision(Long.valueOf(rs.getLong("ID")), rs.getString("NAME"), conference);
            }
            rs.close();
        } catch (SQLException e) {
            throw new ValidationException(e.getMessage() + " in buildDivision()");
        } finally {
            rs = null;            
        }
        return division;
    }

	public Division create(Division division) {
		String sql = "INSERT INTO " + getUnderlyingTableName() + " (NAME, CONFERENCE_ID) VALUES (" 
		+ StringUtil.addSingleQuotes(division.getName()) + ","
		+ division.getConference().getId() + ");";
        return findById(super.create(sql));
	}
	
	public Division findById(Long id) {
        String findByIdQuery = getFindByIdQuery(id);
        return buildDivision(findByIdQuery);
    }

	public Division findByNameAndConference(String name, Conference conference) {
		String sql = "SELECT * FROM " + getUnderlyingTableName() + " WHERE NAME = " + StringUtil.addSingleQuotes(name) + " AND CONFERENCE_ID = " + conference.getId() + ";";
		return buildDivision(sql);
	}

}
